package system;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.lowagie.text.xml.xmp.LangAlt;

import connect.ConnectionHelper;

import entities.Menu;
import entities.RoleMenu;
import entities.Roles;
import entities.Roletrans;
import entities.Users;
import common.Encryption;
import common.Utility;


public class SystemService {
	
	
	public String insert_sys_log(String strAction, String strFrmAction, String strBtnAction,
			String strUserId) throws Exception
	{
		String strResult = "Error";
		Connection c = null;		
		try
		{			
			c = ConnectionHelper.getConnection();							
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call insert_syslog(?,?,?,?,?) }");
            
            
            proc_stmt.setString(1, strAction);   
            proc_stmt.setString(2, strFrmAction);
            proc_stmt.setString(3, strBtnAction);
            proc_stmt.setString(4, strUserId);
            
            proc_stmt.registerOutParameter(5, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(5);               
             																														
		} 
		catch (Exception e) 
		{
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);		
		}
		
		return strResult;
	}
	public String updatePassWord(String strUserId, String strPassWord) throws Exception
	{		
		String strResult = "Error";
		Connection c = null;		
		try
		{			
			c = ConnectionHelper.getConnection();							
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call updatepassword(?,?,?) }");
            
            proc_stmt.setString(1, strUserId);
            proc_stmt.setString(2, strPassWord);            
            proc_stmt.registerOutParameter(3, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(3);               
             																														
		} 
		catch (Exception e) 
		{
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);		
		}
		
		return strResult;
	}
	public List<Roletrans> getRoleTransForUsers(String userID) throws Exception{
		List<Roletrans> list = new ArrayList<Roletrans>();
		Connection c = null;
		try 
		{
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select * from roles join "
								       	+ " (select  users.userid, roleid from users join userrole on users.userid = userrole.userid) bb "
								       	+ " on roles.roleid = bb.roleid " 
								       	+ " where bb.userid = '" + userID + "'");
			while (rs.next()) {
				list.add(new Roletrans(rs.getString("userid"),
						rs.getString("r_add"),
						rs.getString("r_delete"),
						rs.getString("r_edit"),
						rs.getString("r_view"),
						rs.getString("r_author"),
						rs.getString("r_export"),
						rs.getString("r_import")						
						)
						);								
			}		
		} 
		catch (Exception e) 
		{
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);
		}
		return list;
	}
	
	public List<Roletrans> getRoleTransForUsersEdit(String RoleID) throws Exception{
		List<Roletrans> list = new ArrayList<Roletrans>();
		Connection c = null;
		try 
		{			
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select * from  roles where roles.roleid ='" + RoleID + "'");
			while (rs.next()) {
				list.add(new Roletrans(rs.getString("roleid"),
						rs.getString("r_add"),
						rs.getString("r_delete"),
						rs.getString("r_edit"),
						rs.getString("r_view"),
						rs.getString("r_author"),
						rs.getString("r_export"),
						rs.getString("r_import")						
						)
						);								
			}		
		} 
		catch (Exception e) 
		{
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);
		}
		return list;
	}
	
	public String getBranchIDForUser(String userID) throws Exception {
		String strResult = "";
		Connection c = null;
		try {							
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select nvl(branch,'068') branch , branch_name from users join branch on  users.branch = branch.branch_id where userid ='" + userID + "'");			
			while (rs.next()) 
			{
				strResult = rs.getString("branch") + " - " + rs.getString("branch_name");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return strResult;
	}
	
	public List<Menu> getMenu(String userID) throws Exception {

		List<Menu> list = new ArrayList<Menu>();
		Connection c = null;
		try {							
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select menuid,menuname,actionname,parentid,menulevel from menu where menuid in (select menuid from rolemenu" 
										+ " where roleid in (select roleid from userrole where userid = '" + userID + "'))"
										+ " order by menuid");			
			while (rs.next()) {
				list.add(new Menu(rs.getString("menuid"),
						rs.getString("menuname"),
						rs.getString("actionname"),
						rs.getString("parentid"),
						rs.getString("menulevel")));								
			}
			
		} catch (SQLException e) {
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;
	}
	
	
	
	public List<Users> getUsers(String userID, String roleID) throws Exception {

		List<Users> list = new ArrayList<Users>();
		Connection c = null;
		try {										
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery("select u.userid,u.fullname,u.status,u.usercreate,u.datecreate,u.usermodify,u.datemodify,r.roleid,rolename, branch from users u inner join userrole r on u.userid = r.userid inner join roles s on r.roleid = s.roleid"
					+ " where u.userid like '%" + userID + "%' and (r.roleid = '" + roleID + "' or '" + roleID + "' = 'ALL') /*and u.status not in ('D')*/  order by u.userid");										
			while (rs.next()) {
				list.add(new Users(rs.getString("userid"),
						rs.getString("fullname"),
						rs.getString("status"),
						rs.getString("usercreate"),
						Utility.formatSqlAsSimpleDate(rs.getDate("datecreate"), "dd/MM/yyyy"),
						rs.getString("usermodify"),
						Utility.formatSqlAsSimpleDate(rs.getDate("datemodify"), "dd/MM/yyyy"),
						rs.getString("roleid"),
						rs.getString("rolename"),
						rs.getString("branch")));								
			}			
		} catch (SQLException e) {
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;
	}
	

	public List<Roles> getRolesByRolename(String roleName) throws Exception {

		List<Roles> list = new ArrayList<Roles>();
		Connection c = null;
		try {										
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select roleid,rolename,description,usercreate,datecreate,usermodify,datemodify,status from roles" 
										+ " where status = 'A' and rolename like '%" + roleName + "%' or '" + roleName + "' = 'ALL' order by rolename");										
			
			while (rs.next()) {
				list.add(new Roles(rs.getString("roleid"),
						rs.getString("rolename"),
						rs.getString("description"),
						rs.getString("usercreate"),
						Utility.formatSqlAsSimpleDate(rs.getDate("datecreate"), "dd/MM/yyyy"),
						rs.getString("usermodify"),
						Utility.formatSqlAsSimpleDate(rs.getDate("datemodify"), "dd/MM/yyyy"),
						rs.getString("status")));								
			}			
		} catch (SQLException e) {
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;
	}
	
	


	public String Signin(String userID, String password, String strPssNO) throws Exception {
		String strResult = "N";
		Connection c = null;			
		try
		{	
			c = ConnectionHelper.getConnection();			
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select * from users" 
										+ " where userid = '" + userID + "' and status = 'A'");										
			while (rs.next()) 
			{
				strResult = "Y";					
			}
			
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);		
		}			
		return strResult;
	}
	

	
	public String updateUser(String type, String userID, String password, String fullName,
			String status, String roleId, String userAction, String branch, String strPassNoEncrypt) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{			
			Encryption iss = new Encryption();
			c = ConnectionHelper.getConnection();							
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call useraddupdate(?,?,?,?,?,?,?,?,?,?) }");
            
            proc_stmt.setString(1, type);
            proc_stmt.setString(2, userID);
            proc_stmt.setString(3, password);
            proc_stmt.setString(4, fullName);
            proc_stmt.setString(5, status);
            proc_stmt.setString(6, roleId);
            proc_stmt.setString(7, userAction);
            proc_stmt.setString(8, branch);
            proc_stmt.setString(9, iss.tripleDESpwd(strPassNoEncrypt, userID).toString());
            proc_stmt.registerOutParameter(10, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(10);                
             																														
		} catch (SQLException e) {
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}
	
	public String deleteUser(String userID, String userAction) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{
			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call userdelete(?,?,?) }");
            
            proc_stmt.setString(1, userID);
            proc_stmt.setString(2, userAction);
            proc_stmt.registerOutParameter(3, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(3);                
             																														
		} catch (SQLException e) {
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}
	
	public List<RoleMenu> getMenuidByRoleid(String roleID) throws Exception {
		
		List<RoleMenu> list = new ArrayList<RoleMenu>();
		Connection c = null;
		try {										
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select roleid, menuid from rolemenu" 
					+ " where roleid = '" + roleID + "'");										
			
			while (rs.next()) {
				list.add(new RoleMenu(rs.getString("roleid"),
						rs.getString("menuid")));				
			}			
		} catch (SQLException e) {
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;				
	}	
	
	
	public String getRoleTrans(String roleID)throws Exception {
		String list = "";
		Connection c = null;
		try {										
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();					
			ResultSet rs = s.executeQuery("select * from roletrans" 
					+ " where roleid = '" + roleID + "'");										
			
			while (rs.next()) 
			{
				list = "#" + rs.getString("r_add") + "#" + rs.getString("r_delete") + "#"+ rs.getString("r_edit") + "#"+ rs.getString("r_view") + "#"+ rs.getString("r_approve") 
						+ "#" + rs.getString("r_export") + "#" + rs.getString("r_import")  ;
			}			
		}
		catch (SQLException e) 
		{
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);
		}
		return list;			
	}
	
	
	public String updateRole(String roleID, String roleName, String description,
			String menuList, String userAction, String p_add, String p_delete, String p_edit, String p_view,
			String p_approve, String p_export, String p_import) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{

			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call roleaddupdate(?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            
            proc_stmt.setString(1, roleID);
            proc_stmt.setString(2, roleName);
            proc_stmt.setString(3, description);
            proc_stmt.setString(4, menuList);
            proc_stmt.setString(5, userAction);
            
            proc_stmt.setString(6, p_add);
            proc_stmt.setString(7, p_delete);
            proc_stmt.setString(8, p_edit);
            proc_stmt.setString(9, p_view);
            proc_stmt.setString(10, p_approve);
            proc_stmt.setString(11, p_export);
            proc_stmt.setString(12, p_import);
            
            
            proc_stmt.registerOutParameter(13, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(13);                
             																														
		} 
		catch (SQLException e) 
		{
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);		
		}
		
		return strResult;
	}
	

	public String deleteRole(String roleID, String userAction) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{			
			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call roledelete(?,?,?) }");
            
            proc_stmt.setString(1, roleID);
            proc_stmt.setString(2, userAction);
            proc_stmt.registerOutParameter(3, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(3);                
             																														
		} catch (SQLException e) {
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}
	
	public String updateAccount(String type,  String glAcc, String casaAcc, String description,
			String strBrn, String userAction) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{
			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call accountaddupdate(?,?,?,?,?,?,?) }");
            
			proc_stmt.setString(1, type);
			proc_stmt.setString(2, glAcc);
            proc_stmt.setString(3, casaAcc);
            proc_stmt.setString(4, description);
            proc_stmt.setString(5, strBrn);
            proc_stmt.setString(6, userAction);            
            proc_stmt.registerOutParameter(7, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(7);                
             																														
		} 
		catch (SQLException e) 
		{
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}
	
	public String deleteAccount(String casaacc, String userAction) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{			
			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call accountdelete(?,?,?) }");
            
            proc_stmt.setString(1, casaacc);
            proc_stmt.setString(2, userAction);
            proc_stmt.registerOutParameter(3, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(3);                
             																														
		} catch (SQLException e) {
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}

	public String getParentFullChild(String roleID) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{			
			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call getparentfullchid(?,?) }");						
            proc_stmt.setString(1, roleID);
            proc_stmt.registerOutParameter(2, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(2);            
            
		} 
		catch (SQLException e) 
		{
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} 
		finally 
		{
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}	

	
}
